package benchmarks.tpch.mysql.queries;

public class Q22MySQL extends GenericQueryMySQL {
    public Q22MySQL() {
        super.queryName = queryName;
        super.query = query;
    }
    public static final String queryName = "Q22";
    public static final String query =
            "select\n" +
            "\tcntrycode,\n" +
            "\tcount(*) as numcust,\n" +
            "\tsum(c_acctbal) as totacctbal\n" +
            "from\n" +
            "\t(\n" +
            "\t\tselect\n" +
            "\t\t\tsubstring(c_phone from 1 for 2) as cntrycode,\n" +
            "\t\t\tc_acctbal\n" +
            "\t\tfrom\n" +
            "\t\t\tcustomer\n" +
            "\t\twhere\n" +
            "\t\t\tsubstring(c_phone from 1 for 2) in\n" +
            "\t\t\t\t('13', '31', '23', '29', '30', '18', '17')\n" +
            "\t\t\tand c_acctbal > (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\tavg(c_acctbal)\n" +
            "\t\t\t\tfrom\n" +
            "\t\t\t\t\tcustomer\n" +
            "\t\t\t\twhere\n" +
            "\t\t\t\t\tc_acctbal > 0.00\n" +
            "\t\t\t\t\tand substring(c_phone from 1 for 2) in\n" +
            "\t\t\t\t\t\t('13', '31', '23', '29', '30', '18', '17')\n" +
            "\t\t\t)\n" +
            "\t\t\tand not exists (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\t*\n" +
            "\t\t\t\tfrom\n" +
            "\t\t\t\t\torders\n" +
            "\t\t\t\twhere\n" +
            "\t\t\t\t\to_custkey = c_custkey\n" +
            "\t\t\t)\n" +
            "\t) as custsale\n" +
            "group by\n" +
            "\tcntrycode\n" +
            "order by\n" +
            "\tcntrycode";
}
